package exportExcel;

import java.util.ArrayList;
import java.util.Enumeration;
import java.util.HashMap;
import java.util.Locale;
import utils.DateUtils;
import utils.StringUtils;
import utils.UtilsOnScopes;
import javax.servlet.http.*;
import java.io.*;
import java.util.*;

import jxl.*;
import jxl.write.Number;
import jxl.write.*;

public class ExportExcel {
	
	public static String realpath = ""; 
	public static String pathDownload="";
    public static String fileNameDownload ="";
	/**************************************
	 * Method name		: exportJExcel_HaveGroup
	 * Return type		: void
	 * Description		: Xuat ra file Excel
	 * Created date		: Jul 26, 2008
	 * Author			: Honglk
	 **************************************/
	public static void exportJExcel_HaveGroup(String parentDir_Template, String xmlFileName, 
			String parentDir_Download,
			ArrayList<HashMap<String, Object>> listObject, 
			HttpServletRequest request, 
			Hashtable<String, String> hashHeading, 
			Hashtable<String, String> hashEnding) {
		
		String xmlFilePath = getPhysicPathReports(parentDir_Template, xmlFileName);
		// Lay len du lieu cua KEYNAME, DATATYPE
		int keyNameCount = XmlReader.countXMLElements(xmlFilePath,
				ReportConstants.KEYNAME);
		String keyName[] = new String[keyNameCount];
		String dataType[] = new String[keyNameCount];
		int zz = 1;
		try {
			for (int i = 0; i < keyNameCount; i++) {
				keyName[i] = XmlReader.getValueFromPosition(xmlFilePath,
						ReportConstants.KEYNAME, zz);
				dataType[i] = XmlReader.getValueFromPosition(xmlFilePath,
						ReportConstants.DATATYPE, zz);
				zz += 2;
			}
		} catch (Exception ex) {
			ex.printStackTrace();
			System.out
					.println("Recheck DATATYPE element, please! DATATYPE must be valid, and number of DATATYPE subelements must equal number of KEYNAME subelements.");
		}
		// ***********************************

		// Lay len group
		int groupByCount = XmlReader.countXMLElements(xmlFilePath,
				ReportConstants.GROUPBY);
		String groupBy[] = new String[groupByCount];
		String groupByLabel[] = new String[groupByCount];
		String groupByTemp[] = new String[groupByCount];
		String groupSumPosition[] = new String[groupByCount];
		String groupBy_current[] = new String[groupByCount];
		String groupBy_old[] = new String[groupByCount];
		String groupBy_current_next[] = new String[groupByCount];
		String groupBy_old_next[] = new String[groupByCount];
		zz = 1;
		try {
			for (int i = 0; i < groupByCount; i++) {
				// Group boi cai gi?
				groupBy[i] = XmlReader.getValueFromPosition(xmlFilePath,
						ReportConstants.GROUPBY, zz);
				// Label cua group
				groupByLabel[i] = XmlReader.getValueFromPosition(xmlFilePath,
						ReportConstants.GROUPBY_LABEL, zz);
				// Dong mau cua group
				groupByTemp[i] = XmlReader.getValueFromPosition(xmlFilePath,
						ReportConstants.GROUP_TEMP_POSITION, zz);
				// Vi tri dat group (tren, duoi), 0: tren(default), 1: duoi
				groupSumPosition[i] = XmlReader.getValueFromPosition(
						xmlFilePath, ReportConstants.GROUP_SUM_POSITION, zz);
				zz += 2;
			}
		} catch (Exception ex) {
			ex.printStackTrace();
		}
		// ***********************************

		// Lay len sum all
		int sumAllCount = XmlReader.countXMLElements(xmlFilePath,
				ReportConstants.SUMALL);
		double sumAllValue[] = new double[sumAllCount];
		String sumAll[] = new String[sumAllCount];
		zz = 1;
		for (int i = 0; i < sumAllCount; i++) {
			// bien sumAll: chua cac vi tri can tinh sum all
			sumAll[i] = XmlReader.getValueFromPosition(xmlFilePath,
					ReportConstants.SUMALL, zz);
			zz += 2;
		}
		// *********************************

		// Lay len sum group
		int sumGroupCount = XmlReader.countXMLElements(xmlFilePath,
				ReportConstants.GROUPBY_SUM);
		// sumGroupValue[][]: co 2 chieu dai: dem xem co bao nhieu phan tu can
		// tinh sum,
		// va co bao nhieu loai phan group
		double sumGroupValue[][] = new double[groupByCount][sumGroupCount];
		double sumElement[][] = new double[groupByCount][sumGroupCount];
		int countRecords[] = new int[groupByCount];
		String sumGroupOf[] = new String[sumGroupCount];
		zz = 1;
		for (int i = 0; i < sumGroupCount; i++) {
			// bien sumAll: chua cac vi tri can tinh sum all
			sumGroupOf[i] = XmlReader.getValueFromPosition(xmlFilePath,
					ReportConstants.GROUPBY_SUM, zz);
			zz += 2;
		}
		// *********************************

		// Lay len cac input_param can dua vao Excel
		int inputParamCount = XmlReader.countXMLElements(xmlFilePath,
				ReportConstants.INPUT_PARAM);
		String inputParamName[] = new String[inputParamCount];
		zz = 1;
		for (int i = 0; i < inputParamCount; i++) {
			inputParamName[i] = XmlReader.getValueFromPosition(xmlFilePath,
					ReportConstants.INPUT_PARAM, zz);
			zz += 2;
		}

		// Lay len cac postion chung can thiet
		int _STARTHEADER = XmlReader.getInt(xmlFilePath,
				ReportConstants.POSITION, ReportConstants.STARTHEADER, 0);
		int _ENDHEADER = XmlReader.getInt(xmlFilePath,
				ReportConstants.POSITION, ReportConstants.ENDHEADER, 0);
		int _DETAIL_TEMP = XmlReader.getInt(xmlFilePath,
				ReportConstants.POSITION, ReportConstants.DETAIL_TEMP, 0);
		int _STARTFOOTER_TEMP = XmlReader.getInt(xmlFilePath,
				ReportConstants.POSITION, ReportConstants.STARTFOOTER_TEMP, 0);
		int _ENDFOOTER_TEMP = XmlReader.getInt(xmlFilePath,
				ReportConstants.POSITION, ReportConstants.ENDFOOTER_TEMP, 0);
		int _SUMALL_TEMP = XmlReader.getInt(xmlFilePath,
				ReportConstants.POSITION, ReportConstants.SUMALL_TEMP, 0);
		int _SUMALL_POSITION = XmlReader.getInt(xmlFilePath,
				ReportConstants.POSITION, ReportConstants.SUMALL_POSITION, 0);

		String _EXC_PARENT_DIRECTORY = XmlReader.getString(xmlFilePath,
				ReportConstants.OTHERS, ReportConstants.EXC_PARENT_DIRECTORY,
				"");
		String _EXC_IN_FILENAME = XmlReader.getString(xmlFilePath,
				ReportConstants.OTHERS, ReportConstants.EXC_IN_FILENAME, "");
		String _EXC_OUT_FILENAME = XmlReader.getString(xmlFilePath,
				ReportConstants.OTHERS, ReportConstants.EXC_OUT_FILENAME, "");
		String _RECORD_STRING = XmlReader.getString(xmlFilePath,
				ReportConstants.OTHERS, ReportConstants.RECORD_STRING, "");

		try {
			// Khai bao file template excel
			String excelFilePath = getPhysicPathReports(_EXC_PARENT_DIRECTORY,
					_EXC_IN_FILENAME);
			
			// download file template ve: loi cho nay
			
			// Khai bao cac object can thiet cho excel
			JExcelUtils baseRP = new JExcelUtils();
			FileInputStream fIn = new FileInputStream(excelFilePath);
			WorkbookSettings ws = new WorkbookSettings();
			ws.setLocale(new Locale("en", "EN"));
			// Lay workbook template len
			Workbook tempWB = Workbook.getWorkbook(fIn, ws);
			//Luu xuong file
			String path= getPhysicPathReport_Save(parentDir_Download, _EXC_OUT_FILENAME);
			
			//duong dan de download
			setPathDownload(getPhysicPathReportDownload(parentDir_Download, getFileNameDownload()));
			//END
		    File file = new File(path);
			WritableWorkbook wb = Workbook.createWorkbook(file, tempWB);
			
		//END download
			
			// Lay Sheet
			WritableSheet s0 = wb.getSheet(0);
			WritableSheet s1 = null;
			String strTemp = "", strParamValue = "";
			int[] pGroupRow = new int[groupByCount];
			int pGroupRowTemp = 0;
			int sumAllRow = 0;

			// remove cac sheet ngoa`i template sheet ra
			// boi vi sheet ko tao ra tu createSheet() se ko su dung duoc
			int numOfSheet = wb.getNumberOfSheets();
			for (int i = 1; i < numOfSheet; i++) {
				try {
					wb.removeSheet(i);
				} catch (Exception ex) {
					continue;
				}
			}
			// sau khi remove cac sheet du thi tao ra sheet moi
			s1 = wb.createSheet("Sheet 2", 2);
			// set cac thuoc tinh cho executing sheet
			baseRP.setSheetSettingsFromTemplate(s0, s1);

			// write Header
			int currentRow = 0;
			Cell[] rows = null;
			Enumeration<String> e;
			baseRP.copyRows(s0, s1, _STARTHEADER, _ENDHEADER, currentRow); // copy
																			// Header
																			// tu
																			// templae
			if (_ENDHEADER >= 0) {
				for (int i = _STARTHEADER; i <= _ENDHEADER; i++) {
					if (i > 0) {
						currentRow++;
					}
					// ***Chua cho de lam cac thong so cho header
					rows = s1.getRow(i);
					for (int a = 0; a < rows.length; a++) {
						strTemp = s1.getCell(a, currentRow).getContents()
								.toString().trim();
						if (strTemp.indexOf('#') != -1
								&& (hashHeading != null && hashHeading.size() > 0)) {
							e = hashHeading.keys();
							while (e.hasMoreElements()) {
								strParamValue = (String) e.nextElement();
								strTemp = strTemp
										.replaceAll("#" + strParamValue + "#",
												(String) hashHeading
														.get(strParamValue));
							}
							if (StringUtils.StringToDoub(strTemp) == -1) {
								s1.addCell(new Label(a, currentRow, strTemp, s0
										.getCell(a, i).getCellFormat()));
							} else {
								s1
										.addCell(new Number(a, currentRow,
												StringUtils
														.StringToDoub(strTemp),
												s0.getCell(a, i)
														.getCellFormat()));
							}
						}
					}
					// ***
				}
			}
			// Neu vi tri Tong cong o tren cung: write dong sum all
			if (_SUMALL_POSITION == 0) {
				currentRow++;
				sumAllRow = currentRow;
				baseRP.copyRows(s0, s1, _SUMALL_TEMP, _SUMALL_TEMP, sumAllRow);
			}

			// write Details
//			Map<String, Object> hashtable, hashtable_next;
			HashMap<String, Object> hash,hash_next;
			for (int i = 0; i < listObject.size(); i++) {
				hash = (HashMap<String, Object>)  listObject.get(i);
				// phan group
				if (groupByCount > 0) {
					// Dung vong for de tao cac group phan cach
					for (int ii = 0; ii < groupByCount; ii++) {
						// Dau tien gan cho groupBy_old = -1
						if (i == 0) {
							groupBy_old[ii] = "";
						}
						if (i > 17) {
							int z = 0;
						}

						groupBy_current[ii] = (String)hash.get(groupBy[ii]);
						if (!groupBy_old[ii].equals(groupBy_current[ii])) {
							currentRow++;
							baseRP.copyRows(s0, s1, StringUtils.StringToInt(
									groupByTemp[ii], 0), StringUtils
									.StringToInt(groupByTemp[ii], 0),
									currentRow);
							s1.addCell(new Label(0, currentRow,
									(String) hash.get(groupByLabel[ii]),
									s0.getCell(
											0,
											StringUtils.StringToInt(
													groupByTemp[ii], 0))
											.getCellFormat()));
							pGroupRow[ii] = currentRow;
							pGroupRowTemp = StringUtils.StringToInt(
									groupByTemp[ii], 0);
							for (int iii = ii; iii < groupByCount; iii++) {
								// reset lai bien dem 0;
								countRecords[iii] = 0;
								// Reset lai bien old
								groupBy_old[iii] = groupBy_current[iii];
							}
						}
					}
				}
				// ***************
				currentRow++;
				baseRP.copyRows(s0, s1, _DETAIL_TEMP, _DETAIL_TEMP, currentRow);
				// in ra tung cell
				for (int z = 0; z < keyNameCount; z++) {
					/**
					 * *Cac cell dac biet
					 * 
					 * @STT: cell so thu tu
					 */
					if ((keyName[z]).indexOf("@stt") != -1) {
						s1.addCell(new Number(z, currentRow, i + 1, s0.getCell(
								z, _DETAIL_TEMP).getCellFormat()));
						z++;
					}
					/** *************** */

					// Viet ra cac cell binh thuong
//					System.out.println("cot xml:"+keyName[z]);
//					System.out.println("cot csdl:"+ hash
//							.get(keyName[z]).toString());
					if (StringUtils.StringToInt(dataType[z], 0) == 0) {
						s1.addCell(new Label(z, currentRow, (String) hash
								.get(keyName[z]), s0.getCell(z, _DETAIL_TEMP)
								.getCellFormat()));
						
					}//Modify(Honglk):25_7_2008: trong truong hop la kieu ngay 
					else if(StringUtils.StringToInt(dataType[z], 0) == 2){
						String ngaycsdl = hash.get(keyName[z]).toString();
						String ngay = DateUtils.formatDate(ngaycsdl);
						s1.addCell(new Label(z, currentRow, ngay, s0.getCell(z, _DETAIL_TEMP)
								.getCellFormat()));
					}//Trong truong la kieu boolean
					else if(StringUtils.StringToInt(dataType[z], 0) == 3){
						String temp = (hash.get(keyName[z]).toString().equals("true"))?"X":"";
						s1.addCell(new Label(z, currentRow, temp, s0.getCell(z, _DETAIL_TEMP)
								.getCellFormat()));
					}
					//End
					else {
						s1.addCell(new Number(z, currentRow, StringUtils
								.StringToDoub((String) hash
										.get(keyName[z])), s0.getCell(z,
								_DETAIL_TEMP).getCellFormat()));
					}
				}
				// Tinh sumall
				if (sumAllCount > 0) {
					for (int ii = 0; ii < sumAllCount; ii++) {
						// Neu vong for nay la first thi gan cac phan tu bang 0;
						if (i == 0) {
							sumAllValue[ii] = 0;
						}
						sumAllValue[ii] += StringUtils.StringToDoub(
								(String) hash.get(keyName[StringUtils
										.StringToInt(sumAll[ii], 0)]), 0);
					}
				}
				// Tinh sum theo group
				if (sumGroupCount > 0 & groupByCount > 0) {
					// Thuc hien vong for theo so group (VD: group theo
					// MSPHONGBAN, MSBOPHAN)
					// for (int ii = 0; ii < groupByCount; ii++) {
					for (int ii = groupByCount - 1; ii >= 0; ii--) {
						// Tang bien dem
						countRecords[ii]++;
						// Reset lai 2 bien next
						groupBy_old_next[ii] = groupBy_old[ii];
						groupBy_current_next[ii] = "";
						if ((i + 1) < listObject.size()) {
							hash_next = (HashMap<String, Object>) listObject.get(i + 1);
							groupBy_current_next[ii] = (String) hash_next
									.get(groupBy[ii]);
						}
						if (!groupBy_current_next[ii]
								.equals(groupBy_current[ii])) {
							// neu vi tri dat groupsum o duoi
							if (StringUtils.StringToInt(groupSumPosition[ii]) == 1) {
								currentRow++;
								baseRP
										.copyRows(s0, s1,
												StringUtils.StringToInt(
														groupByTemp[ii], 0),
												StringUtils.StringToInt(
														groupByTemp[ii], 0),
												currentRow);
								s1.addCell(new Label(0, pGroupRow[ii], "", s0
										.getCell(
												0,
												StringUtils.StringToInt(
														groupByTemp[ii], 0))
										.getCellFormat()));
							}
						}
						// Thuc hien vong for theo cac sum can tinh
						for (int xx = 0; xx < sumGroupCount; xx++) {
							if (i == 0) {
								sumElement[ii][xx] = 0;
							}
							sumElement[ii][xx] += StringUtils.StringToDoub(
									(String) hash.get(keyName[StringUtils
											.StringToInt(sumGroupOf[xx], 0)]),
									0);
							if (!groupBy_current_next[ii]
									.equals(groupBy_current[ii])) {
								if (i == 0) {
									sumGroupValue[ii][xx] = 0;
								}
								if (_RECORD_STRING.trim().equals("") == false) {
									if (xx == 0) {
										// Cell dem records
										strTemp = s1.getCell(0, pGroupRow[ii])
												.getContents();
										strTemp = strTemp + " ("
												+ countRecords[ii] + " "
												+ _RECORD_STRING + ")";
										s1.addCell(new Label(0, pGroupRow[ii],
												strTemp, s0.getCell(0,
														pGroupRowTemp)
														.getCellFormat()));
									}
								}
								// Ghi sum ra Excel
								s1
										.addCell(new Number(
												(short) StringUtils
														.StringToInt(sumGroupOf[xx]),
												pGroupRow[ii],
												sumElement[ii][xx],
												s0
														.getCell(
																StringUtils
																		.StringToInt(sumGroupOf[xx]),
																pGroupRowTemp)
														.getCellFormat()));
								sumElement[ii][xx] = 0;
							}
						}
					}
				}
			}
			// write dong sum all
			if (sumAllCount > 0) {
				if (_SUMALL_POSITION != 0) {
					currentRow++;
					sumAllRow = currentRow;
					baseRP.copyRows(s0, s1, _SUMALL_TEMP, _SUMALL_TEMP,
							currentRow);
				}

				for (int i = 0; i < sumAllCount; i++) {
					s1.addCell(new Number((short) StringUtils.StringToInt(
							sumAll[i], 0), sumAllRow, sumAllValue[i], s0
							.getCell(StringUtils.StringToInt(sumAll[i], 0),
									_SUMALL_TEMP).getCellFormat()));
				}
			}
			// write cac dong ky/footer
			if (_ENDFOOTER_TEMP >= 0) {
				currentRow++;
				baseRP.copyRows(s0, s1, _STARTFOOTER_TEMP, _ENDFOOTER_TEMP,
						currentRow);
				for (int i = _STARTFOOTER_TEMP+1; i <= _ENDFOOTER_TEMP; i++) {
					currentRow++;
					rows = s1.getRow(currentRow);
					for (int a = 0; a < rows.length; a++) {
						strTemp = s1.getCell(a, currentRow).getContents()
								.toString().trim();
						if (strTemp.indexOf('#') != -1 && hashEnding != null && hashEnding.size() > 0) {
							e = hashEnding.keys();
							while (e.hasMoreElements()) {
								strParamValue = (String) e.nextElement();
								strTemp = strTemp.replaceAll("#"
										+ strParamValue + "#",
										(String) hashEnding.get(strParamValue));
							}
							System.out.println("a:"+a);
							System.out.println("i :"+i);
							System.out.println("cell :"+s0.getCell(a, 13));
							System.out.println("noi dung :"+strTemp);
							System.out.println("curren row :"+currentRow);
							
							s1.addCell(new Label(a, currentRow, strTemp, s0
									.getCell(a, i).getCellFormat()));
						
						}
					}
				}
			}
			
			
			// Delete template sheet
			if (wb.getNumberOfSheets() > 1) {
				wb.removeSheet(0);
			}
			wb.write();
			wb.close();
			fIn.close();
		} catch (Exception ex) {
			ex.printStackTrace();
		}
	}

	/**************************************
	 * Method name		: getPhysicPathReports
	 * Return type		: String
	 * Decription		:Lay duong dan chi den file xml
	 * Created date		: Jul 26, 2008
	 * Author			: Honglk
	 **************************************/
	public static String getPhysicPathReports(String sTenModule, String sTenFile) {
		String _sPath = "";

		String _sRootPath = UtilsOnScopes.getSession().getServletContext()
				.getRealPath("");

		sTenModule = sTenModule.toLowerCase();
		_sPath = _sRootPath + "\\reports\\" + sTenModule + "\\" + sTenFile;
		System.out.println("path:" +_sPath);
		return _sPath;
	}
	/**************************************
	 * Method name		: getPhysicPathReport_Save
	 * Return type		: String
	 * Decription		:Luu xuong 
	 * Created date		: Jul 26, 2008
	 * Author			: Honglk
	 **************************************/
	public static String getPhysicPathReport_Save(String sTenModule, String sTenFile) {
		String _sPath = "";
		String _sRootPath = UtilsOnScopes.getSession().getServletContext()
		.getRealPath("");
		int indexOfStr = sTenFile.indexOf(".");
		String strFirst = sTenFile.substring(0,indexOfStr);
		String strLast = sTenFile.substring(indexOfStr);
		String temp = DateUtils.LayNgayThangHienTaiGio1();
		String sTenFileTemp = strFirst +"-"+temp +strLast;
		
		String sTenFileTempEnd = "";
		StringTokenizer token = new StringTokenizer(sTenFileTemp," ,:");
		sTenFileTempEnd = token.nextToken()+"-"+token.nextToken()+"-"+token.nextToken()
		+"-"+token.nextToken();
		
		setFileNameDownload(sTenFileTempEnd);
		System.out.println(sTenFileTempEnd);
		sTenModule = sTenModule.toLowerCase();
		_sPath = _sRootPath + "\\reports\\" + sTenModule + "\\" + sTenFileTempEnd;
		realpath = _sRootPath + "\\reports\\" + sTenModule;
		System.out.println("path:" +_sPath);
		return _sPath;
	}
	public static void main(String[] args) {
		ExportExcel.getPhysicPathReport_Save("qldoanra", "baocao.xls");
	}
	/**************************************
	 * Method name		: getPhysicPathReportDownload
	 * Return type		: String
	 * Decription		:Lay duong dan chi den file excel de donwload ve
	 * Created date		: Jul 26, 2008
	 * Author			: Honglk
	 **************************************/
	public  static String getPhysicPathReportDownload(String sTenModule, String sTenFile) {
		String _sPath = "";
		sTenModule = sTenModule.toLowerCase();
		String _sRootPath = UtilsOnScopes.getSession().getServletContext().getContextPath();
		_sPath = _sRootPath+"/reports/" + sTenModule + "/" + sTenFile ;
		System.out.println("path download:" +_sPath);
		return _sPath;
	} 

	public static String getPathDownload() {
		return pathDownload;
	}

	public static void setPathDownload(String pathDownload) {
		ExportExcel.pathDownload = pathDownload;
	}

	public static String getFileNameDownload() {
		return fileNameDownload;
	}

	public static void setFileNameDownload(String fileNameDownload) {
		ExportExcel.fileNameDownload = fileNameDownload;
	}

	/****************************************************
	 * Method name		: getRealpath [GETTER]
	 * Return type		: ExportExcel
	 * Description		: 
	 * Created date		: Jul 27, 2008
	 * Author			: Phonglt
	 ****************************************************/
	public static String getRealpath() {
		return realpath;
	}
	
}
